package com.logicwin.aibala.system.sqlite;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.zip.ZipException;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.logicwin.aibala.com.AibalaException;
import com.logicwin.aibala.com.GlobalConfig;
import com.logicwin.aibala.model.androidSWInfo.APKListItem;
import com.logicwin.aibala.model.androidSWInfo.AndroidSWInfoService;
import com.logicwin.aibala.services.file.FileConnection;
import com.logicwin.aibala.services.net.NetConnection;
import com.logicwin.aibala.services.xml.XMLParser;
import com.logicwin.aibala.services.zip.ZipUtil;

/**
 * 数据库服务类
 * 
 * @author GuoDing
 * @since 2011-08-01
 */
public class SQLiteService {
	private SQLiteService() {
	}
	
	private static SQLiteDatabase myDB = null;
	
	static public void init(Context ctx) throws AibalaException {
		myDB = ctx.openOrCreateDatabase(GlobalConfig.getSQLiteDBName(), Context.MODE_PRIVATE, null);
		if(GlobalConfig.isFirstInstall()) {
			createTables();
		}
	}
	
	/**
	 * 获取数据库已有的列表信息
	 * @param sortType
	 * @param classType
	 * @return
	 */
	static public List<APKListItem> getAndroidSWList(String sortType, String classType) {
		List<APKListItem> ret = new ArrayList<APKListItem>();
		
		String[] columns = {"swID"};
		String selection = "where sortType=? and classType=?";
		String[] selectionArgs = {sortType, classType};
		Cursor cursor = myDB.query("APKListItemIndex", columns, selection, selectionArgs, null, null, "id");
		
		List<Integer> idList = new ArrayList<Integer>();
		while(cursor.moveToNext()) {
			idList.add(cursor.getInt(0));
		}
		
		String[] col = {"swID", "apkName", "versionName", "iconPicUrl", "refStarCount", "downloadCount", "apkSize"};
		selection = "where swID=?";
		for(Integer i : idList) {
			String[] args = {String.valueOf(i)};
			cursor = myDB.query("APKListItem", col, selection, args, null, null, null);
			if(cursor.moveToNext()) {
				APKListItem tmp = new APKListItem(i);
				tmp.setAPKName(cursor.getString(1));
				tmp.setVersion(cursor.getString(2));
				tmp.setIconURL(cursor.getString(3));
				tmp.setRefStarCount(cursor.getInt(4));
				tmp.setDownloadCount(cursor.getString(5));
				tmp.setAPKSize(cursor.getString(6));
				ret.add(tmp);
			}
		}
		
		return ret;
	}
	
	/**
	 * 从网络获取下一页内容
	 * @param sortType
	 * @param classType
	 */
    static public void loadNextPage(String sortType, String classType) {
    	int page = getCurrPage(sortType, classType) + 1;
    	String zipPath = NetConnection.httpGet(GlobalConfig.AIBALA_APKLIST_URL + "?" +
    			                               "type=" + classType + "&" +
    			                               "sort=" + sortType + "&" +
    			                               "page=" + page, 
    			                               null);
    	String zipFilename = GlobalConfig.getTMPDownloadDir() + "/" + classType + "_" + sortType + "_" + page + ".zip";
    	NetConnection.downLoadFile(zipPath, zipFilename);
    	try {
			ZipUtil.upZipFile(new File(zipFilename), GlobalConfig.getTMPDownloadDir());
		} catch (Exception e) {
			Log.e("run", "zip=" + zipPath + " zipFilename="+ zipFilename);
			return;
		}
		
		String xmlPath = zipPath.substring(zipPath.lastIndexOf("/") + 1, zipPath.length() - 4) + ".xml";
		List<APKListItem> apkList = XMLParser.parseList(GlobalConfig.getTMPDownloadDir() + "/" + xmlPath);
		for(APKListItem ai : apkList) {
			insertAPKListItem(ai, sortType, classType, page);
		}
    }
	
	/**
	 * 检测是否通过Aibala安装了这个软件
	 * @param pkgName: 唯一标识APK
	 * @return: 如果是Aibala安装，返回swID,否则，-1
	 */
	static public int containAPK(String pkgName) {
		String[] columns = {"swID", "pkgName", "apkName", "versionName"};
		String selection = "where pkgName=?";
		String[] selectionArgs = {pkgName};
		Cursor cursor = myDB.query("APKInstallInfo", columns, selection, selectionArgs, null, null, null);
		
		if(cursor.moveToNext()) {
			return cursor.getInt(0);
		}
		return -1;
	}
	
	static private void insertAPKListItem(APKListItem ai, String sortType, String classType, int page) {
		ContentValues values = new ContentValues();
		values.put("sortType", sortType);
		values.put("classType", classType);
		values.put("pageIndex", page);
		values.put("swID", ai.getID());
		myDB.insert("APKListItemIndex", null, values);
		
		String[] columns = {"swID"};
		String selection = "where swID=?";
		String[] selectionArgs = {String.valueOf(ai.getID())};
		Cursor cursor = myDB.query("APKListItem", columns, selection, selectionArgs, null, null, null);
		
		if(cursor.moveToNext()) {
			return;
		}
		
		values = new ContentValues();
		values.put("swID", ai.getID());
		values.put("apkName", ai.getAPKName());
		values.put("versionName", ai.getVersion());
		values.put("iconPicUrl", ai.getVersion());
		values.put("refStarCount", ai.getRefStarCount());
		values.put("downloadCount", ai.getDownloadCount());
		values.put("apkSize", ai.getAPKSize());
		
		myDB.insert("APKListItem", null, values);
	}
	
	static private int getCurrPage(String sortType, String classType) {
		String[] columns = {"pageIndex"};
		String selection = "where sortType=? and classType=?";
		String[] selectionArgs = {sortType, classType};
		Cursor cursor = myDB.query("APKListItemIndex", columns, selection, selectionArgs, null, null, "pageIndex desc");
		
		if(cursor.moveToNext()) {
			return cursor.getInt(0);
		}
		
		return 0;
	}
	
	static private void createTables() throws AibalaException {
		/**
		 * 软件列表信息表以及swID索引创建
		 */
		myDB.execSQL("CREATE TABLE IF NOT EXISTS APKListItem(id INTEGER(4) PRIMARY KEY AUTOINCREMENT," +
				                                            "swID INTEGER(4) UNIQUE," +
				                                            "apkName TEXT(32) NOT NULL," +
				                                            "versionName TEXT(32) NOT NULL," +
				                                            "iconPicUrl TEXT(256) NOT NULL," +
				                                            "refStarCount INTEGER(4)," +
				                                            "downloadCount TEXT(16) NOT NULL," +
				                                            "apkSize TEXT(16) NOT NULL," +
				                                            "apkFileName TEXT(16) NOT NULL," +
				                                            "packageName TEXT(128) NOT NULL)");
		myDB.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS swID_Index ON APKListItem(swID)");
		
		/**
		 * 索引表
		 */
		myDB.execSQL("CREATE TABLE IF NOT EXISTS APKListItemIndex(id INTEGER(4) PRIMARY KEY AUTOINCREMENT," +
				                                                 "sortType TEXT(32) NOT NULL," +
				                                                 "classType TEXT(32) NOT NULL," +
				                                                 "pageIndex INTEGER(4) NOT NULL," + 
				                                                 "swID INTEGER(4) NOT NULL)");
		myDB.execSQL("CREATE INDEX IF NOT EXISTS sortType_Index ON APKListItemIndex(sortType)");
		
		/**
		 * 通过Aibala安装的APK信息表
		 */
		myDB.execSQL("CREATE TABLE IF NOT EXISTS APKInstallInfo(id INTEGER(4) PRIMARY KEY AUTOINCREMENT," +
                                                                "swID INTEGER(4) UNIQUE," + 
                                                                "pkgName TEXT(64) NOT NULL," + 
                                                                "apkName TEXT(32) NOT NULL," +
                                                                "versionName TEXT(32) NOT NULL)");
		myDB.execSQL("CREATE INDEX IF NOT EXISTS pkgName_Index ON APKInstallInfo(pkgName)");
		
	}
}














